package com.bingo.mpa.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.bingo.mpa.pojo.Phone;

public class PhoneDao
{
	public static long save( Phone obj ) throws SQLException
    {
		final Connection conn = ConnectionPool.getConnection();
		long sid = -1;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "INSERT INTO P_PHONE(AID,CARRIER,BRAND,CARD_TYPE,PHONE,RETAIL,DISCOUNT,PROVINCE,CITY)" + " VALUES(?,?,?,?,?,?,?,?,?)";
        try
        {
        	conn.setAutoCommit(false);
        	pstmt = conn.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
    		int j = 1;
    		pstmt.setLong(j++, obj.getAid());
    		pstmt.setByte(j++, obj.getCarrier());
    		pstmt.setByte(j++, obj.getBrand());
    		pstmt.setByte(j++, obj.getCard_type());
    		pstmt.setString(j++, obj.getPhone());
    		pstmt.setInt(j++, obj.getRetail());
    		pstmt.setInt(j++, obj.getDiscount());
    		pstmt.setString(j++, obj.getProvince());
    		pstmt.setString(j++, obj.getCity());
    		pstmt.execute();
    		rs = pstmt.getGeneratedKeys();
    		conn.commit();
			sid = rs.next() ? rs.getLong(1) : -1;
        }
        catch(Exception e)
        {
        	e.printStackTrace();
        }
        finally
        {
            try
            {
                if( rs != null )
                    rs.close();
                if( pstmt != null )
                	pstmt.close();
                if(conn != null)
                {
                	conn.setAutoCommit(true);
                	conn.close();
                }
            }
            catch (SQLException ex)
            {
            }
        }
        return sid;
    }
	
	public static void update(Phone obj)
	{
		final Connection conn = ConnectionPool.getConnection();
		PreparedStatement pstmt = null;
		String update = "UPDATE P_PHONE SET AID=?,CARRIER=?,BRAND=?,CARD_TYPE=?,RETAIL=?,DISCOUNT=?,PROVINCE=?,CITY=? WHERE PID=?";
        try
        {
        	conn.setAutoCommit(false);
    		pstmt = conn.prepareStatement(update);
    		int j = 1;
    		pstmt.setLong(j++, obj.getAid());
    		pstmt.setByte(j++, obj.getCarrier());
    		pstmt.setByte(j++, obj.getBrand());
    		pstmt.setByte(j++, obj.getCard_type());
    		pstmt.setInt(j++, obj.getRetail());
    		pstmt.setInt(j++, obj.getDiscount());
    		pstmt.setString(j++, obj.getProvince());
    		pstmt.setString(j++, obj.getCity());
    		pstmt.setLong(j++, obj.getPid());
    		pstmt.execute();
    		conn.commit();
        }
        catch(Exception e)
        {
        	e.printStackTrace();
        }
        finally
        {
            try
            {
                if( pstmt != null )
                    pstmt.close();
                if(conn != null)
                {
	                conn.setAutoCommit(true);
	                conn.close();
                }
            }
            catch (SQLException ex)
            {
            }
        }
	}
	
	public static Phone findByPhone(String phone)
	{
		final Connection conn = ConnectionPool.getConnection();
        if(conn == null )
        {
            return null;
        }
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try
        {
            String sql = "SELECT * FROM P_PHONE WHERE PHONE=?";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1,phone);
            rs = stmt.executeQuery();

            if(rs.next())
            {
                Phone obj = new Phone();
                obj.setPid(rs.getLong("PID"));
                obj.setAid(rs.getLong("AID"));
                obj.setCarrier(rs.getByte("CARRIER"));
                obj.setRetail(rs.getInt("RETAIL"));
                obj.setDiscount(rs.getInt("DISCOUNT"));
                obj.setProvince(rs.getString("PROVINCE"));
                obj.setCity(rs.getString("CITY"));
                obj.setStatus(rs.getByte("STATUS"));
                return obj;
            }
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
        finally
        {
            try
            {
                if( rs != null )
                    rs.close();
                if( stmt != null )
                    stmt.close();
                if(conn != null)
                {
                	conn.close();
                }
            }
            catch (SQLException ex)
            {
            }
        }
        return null;
	}
}
